from odoo import fields, models


class ReportWorkOrderTime(models.Model):
    _name = 's.report.work.order.time'
    _description = 'Report Work Order Time'
    _auto = False
    # _order = 'production_id desc'

    production_id = fields.Many2one('mrp.production', string='Production')
    sequence = fields.Integer('Sequence')
    product_id = fields.Many2one('product.product', string='Product')
    station_id = fields.Many2one('work.station', string='Station')
    start_date = fields.Datetime('Station Start Date')
    end_date = fields.Datetime('Station End Date')
    diff_time = fields.Integer('Time(hours)')

    @property
    def _table_query(self):
        where = ''
        product_id = self._context.get('product_id')
        if product_id:
            where += f' and mwo.product_id = {product_id} '
        production_id = self._context.get('production_id')
        if production_id:
            production = self.env['mrp.production'].browse(production_id)
            production_ids = production._get_all_child()
            production_ids |= production

            if production_ids:
                p_str = ','.join(str(i) for i in production_ids.ids)
                where += f' and mwo.production_id in ({p_str}) '
        sql = f"""
            WITH step_sequence AS (SELECT mwo.id,
                              mwo.production_id,
                              mwo.product_id,
                              mwc.work_station_id,
                              mwo.step,
                              mwo.date_start,
                              mwo.date_finished,
                              -- 批次检测
                              CASE
                                  WHEN mwc.work_station_id <>
                                       LAG(mwc.work_station_id) OVER (PARTITION BY mwo.production_id ORDER BY mwo.step)
                                      OR LAG(mwo.production_id)
                                         OVER (PARTITION BY mwo.production_id ORDER BY mwo.step) IS NULL
                                      THEN 1
                                  ELSE 0
                                  END AS is_new_batch
                       FROM mrp_workorder mwo
                                JOIN mrp_workcenter mwc ON mwo.workcenter_id = mwc.id
                                LEFT JOIN work_station ws ON mwc.work_station_id = ws.id
                       WHERE mwo.state = 'done'
                         AND mwc.s_not_compute_output != TRUE
                         {where}),
                 batch_groups AS (SELECT *,
                                         SUM(is_new_batch) OVER (PARTITION BY production_id ORDER BY step) AS batch_id
                                  FROM step_sequence),
                 batch_data AS (SELECT work_station_id                                                             AS station_id,
                                       production_id,
                                       product_id,
                                       batch_id,
                                       MIN(date_start)                                                             AS original_start_date, 
                                       MAX(date_finished)                                                          AS end_date,
                                       -- 获取上一个批次的结束时间
                                       LAG(MAX(date_finished)) OVER (PARTITION BY production_id ORDER BY batch_id) AS prev_end_date
                                FROM batch_groups
                                GROUP BY work_station_id, production_id, product_id, batch_id
                                order by production_id desc, batch_id)
            SELECT ROW_NUMBER() OVER ()                                                                           AS id,
                   batch_id                                                                                    sequence,
                   station_id,
                   production_id,
                   product_id,
                   COALESCE(prev_end_date, original_start_date)                                                   AS start_date,
                   end_date,
                   ROUND(EXTRACT(EPOCH FROM (end_date - COALESCE(prev_end_date, original_start_date))) / 3600, 1) AS diff_time
            FROM batch_data
            
        """
        return sql
